package com.lz.dao;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by lz on 2018/5/19.
 */
@Repository
public class CommonDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * Fund 查询所有基金
     * @param keyword
     * @param pageIndex
     * @param pageSize
     * @param sortType
     * @param field
     */
    public List<Map<String, Object>> queryFund(String keyword, String pageIndex, String pageSize, String sortType, String field){
        if (StringUtils.isNotBlank(keyword)){
            String sql = "select * from fund WHERE NAME LIKE ? ORDER BY " + field + " " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,"%" + keyword + "%",(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        } else {
            String sql = "select * from fund ORDER BY " + field + " " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,(Integer.parseInt(pageIndex) - 1),Integer.parseInt(pageSize));
        }
    }

    /**
     * Current 查询所有活期
     * @return
     * @param keyword
     * @param pageIndex
     * @param pageSize
     * @param sortType
     * @param field
     */
    public List<Map<String, Object>> queryCurrent(String keyword, String pageIndex, String pageSize, String sortType, String field){
        if (StringUtils.isNotBlank(keyword)){
            String sql = "select * from current WHERE NAME LIKE ? ORDER BY " + field +" " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,"%" + keyword + "%",(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        } else {
            String sql = "select * from current ORDER BY " + field + " " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        }
    }

    /**
     * netloan 查询所有网贷
     * @return
     * @param keyword
     * @param pageIndex
     * @param pageSize
     */
    public List<Map<String, Object>> queryNetloan(String keyword, String pageIndex, String pageSize){
        if (StringUtils.isNotBlank(keyword)){
            String sql = "select * from netloan WHERE NAME LIKE ? ORDER BY year_s DESC limit ? , ? ";
            return jdbcTemplate.queryForList(sql,"%" + keyword + "%",(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        } else {
            String sql = "select * from netloan ORDER BY year_s DESC limit ? , ? ";
            return jdbcTemplate.queryForList(sql,(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        }
    }

    /**
     * 查询定期
     * @param keyword
     */
    public List<Map<String, Object>> queryRegular(String keyword){
        if (StringUtils.isNotBlank(keyword)){
            String sql = "select * from regular WHERE NAME LIKE ? ORDER BY minInterestRatePerYear DESC limit 0,20 ";
            return jdbcTemplate.queryForList(sql,"%" + keyword + "%");
        } else {
            String sql = "select * from regular ORDER BY minInterestRatePerYear DESC limit 0,20 ";
            return jdbcTemplate.queryForList(sql);
        }
    }

    /**
     * NetloanAuxiliary  查询网贷备用
     * @return
     */
    public List<Map<String, Object>> queryNetloanAuxiliary(){
        String sql = "select * from netloan_auxiliary ";
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 通过id查询产品
     * @param table
     * @param pId
     */
    public List<Map<String, Object>> queryByProductId(String table, String pId) {

        String sql = "select * from " + table + " where id = ?";
        return jdbcTemplate.queryForList(sql, pId);
    }

    /**
     * 查询所有
     * @param keyword
     * @param i
     * @return
     */
    public List<Map<String, Object>> queryAll(String keyword, int i){
        String table = null;
        String order = null;
        switch (i){
            case 0://活期
                table = "current";
                order = "seven_sy";
                break;
            case 1://定期
                table = "regular";
                order = "minInterestRatePerYear";
                break;
            case 2://基金
                table = "fund";
                order = "new_value";
                break;
            case 3://网贷
                table = "netloan";
                order = "year_s";
                break;
        }
        String sql = "select * from " + table + " where name like ? ORDER BY ? DESC limit 0,20 ";
        return jdbcTemplate.queryForList(sql,"%" + keyword + "%",order);
    }

    /**
     * 通过规则查询定期
     * @param keyword
     * @param initInvestAmount
     * @param investPeriod
     * @param riskLevel
     * @param pageIndex
     * @param pageSize
     * @param sortType
     * @param field
     */
    public List<Map<String, Object>> queryRegularByRule(String keyword, String initInvestAmount, String investPeriod, String riskLevel, String pageIndex, String pageSize, String sortType, String field) {
        if (StringUtils.isNotBlank(keyword)){

            String sql = "select * from regular WHERE NAME LIKE ? AND ";
            if (StringUtils.isNotBlank(riskLevel)){
                sql += " riskLevel = " + riskLevel;
            } else {
                sql += " riskLevel < 5 ";
            }
            if (StringUtils.isNotBlank(initInvestAmount)){
                sql += " and initInvestAmount = " + initInvestAmount;
            }
            if (StringUtils.isNotBlank(investPeriod)){
                sql += " and investPeriod > " + ((Integer.parseInt(investPeriod) * 30) - 10) + " and investPeriod < " + ((Integer.parseInt(investPeriod) * 30) + 10);
            }
            sql += " ORDER BY " + field + " " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,"%" + keyword + "%",(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        } else {
            String sql = "select * from regular WHERE 1 = 1 AND  ";
            if (StringUtils.isNotBlank(riskLevel)){
                sql += " riskLevel = " + riskLevel;
            } else {
                sql += " riskLevel < 5 ";
            }
            if (StringUtils.isNotBlank(initInvestAmount)){
                sql += " and initInvestAmount = " + initInvestAmount;
            }
            if (StringUtils.isNotBlank(investPeriod)){
                sql += " and investPeriod > " + ((Integer.parseInt(investPeriod) * 30) - 10) + " and investPeriod < " + ((Integer.parseInt(investPeriod) * 30) + 10);
            }
            sql += " ORDER BY " + field + " " + sortType + " limit ? , ? ";
            return jdbcTemplate.queryForList(sql,(Integer.parseInt(pageIndex) - 1 ),Integer.parseInt(pageSize));
        }

    }

    /**
     * 查询分页总数
     * @param table
     * @return
     */
    public Map<String, Object> queryNum(String table){
        String sql = "select count(*) as amount from " + table ;
        return jdbcTemplate.queryForMap(sql);
    }
}
